﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Data = System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace Fitment.User
{
    public partial class matBudget : System.Web.UI.Page
    {
        DS data = new DS();
        protected const int start = 3;  //数据填充的起始行
        protected void Page_Load(object sender, EventArgs e)
        {
            //如果Session为空,则转到登陆页.
            if (Session["User_ID"] == null)
            {
                Response.Redirect("../logon.aspx");
            }
            if (Session["House_ID"] == null)
            {
                ClientScript.RegisterStartupScript(Page.GetType(), "", " <SCRIPT>if(confirm('您还没有输入要装修的房子信息,请选择房子进行编辑或者重新输入房子信息！')) window.location.href = 'myHouse.aspx';</SCRIPT>");
            }
            else
            {
                if (!IsPostBack)
                {
                    bindList();
                }
            }
        }
        protected void bindList()
        {
            int House_ID = Convert.ToInt32(Session["House_ID"].ToString());
            /*四个表级联：房子信息表、房间信息表、材料表、选择项表*/
            string strSql = "SELECT ROOMINFO.ROOM_ID AS ROOM_ID, ROOMINFO.ROOMTYPE AS ROOMTYPE, MATERIAL.MATNAME AS MATNAME, PRICE_AREA, PRICE_NUM, SELECTITEM.UNIT_PRICE AS UNIT_PRICE, SELECTITEM.PRICE AS PRICE "
                + "FROM HOUSEINFO, ROOMINFO, MATERIAL, SELECTITEM WHERE HOUSEINFO.HOUSE_ID = ROOMINFO.HOUSE_ID "
                + "AND ROOMINFO.ROOM_ID = SELECTITEM.ROOM_ID AND MATERIAL.MAT_ID = SELECTITEM.MAT_ID "
                + "AND HOUSEINFO.HOUSE_ID = '" + House_ID + "' ORDER BY ROOM_ID ASC";
            Data.DataTable table = data.GetDataSource(strSql, "Info");
            updateBudget(table);
            this.gv_budget.DataSource = table;
            this.gv_budget.DataBind();   //将数据绑定到控件中
        }
        /*更新界面上的最低预算和最终预算*/
        protected void updateBudget(Data.DataTable table)
        {
            /*由table中取出最低参考价、预算单价和预算总价*/
            int count = table.Rows.Count;  //表的行数
            double[] num = new double[count];  //用于存放每项材料的数量
            int[] minP = new int[count];    //最低参考价
            int[] unitP = new int[count];   //预算单价
            int[] totalP = new int[count];  //预算总价
            int min1, min2;   //用于比较，取哪个最低价
            double minSum = 0;
            int bgSum = 0;
            for (int i = 0; i < count; i++)
            {
                min1 = Convert.ToInt32(table.Rows[i]["Price_Area"].ToString());
                min2 = Convert.ToInt32(table.Rows[i]["Price_Num"].ToString());
                if (min1 < min2)
                    minP[i] = min2;
                else
                    minP[i] = min1;
                unitP[i] = Convert.ToInt32(table.Rows[i]["Unit_Price"].ToString());
                totalP[i] = Convert.ToInt32(table.Rows[i]["Price"].ToString());
                num[i] = (double)totalP[i] / (double)unitP[i];
                minSum += minP[i] * num[i];
                bgSum += totalP[i];
            }
            int minSum2 = (int)minSum;
            this.tb_bgMin.Text = minSum2.ToString();
            this.tb_bgRes.Text = bgSum.ToString();
        }
        /*用户确定预算之后*/
        protected void OK_Click(object sender, EventArgs e)
        {
            string strBg = this.tb_bgInput.Text.ToString();
            if (strBg.Equals(""))
            {
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "提示", "<script>alert('请输入您的预算！');</script>");
                return;
            }
            int bgInput = Convert.ToInt32(strBg);
            int min = Convert.ToInt32(this.tb_bgMin.Text.ToString());
            if (bgInput < min)
            {
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "提示", "<script>alert('您输入的总预算低于最低预算，请重新输入或者重新定制装修方案！');</script>");
                return;
            }
            /*根据用户输入更新数据库的预算，更新表SelectItem中的单价和总价*/
            updateDbBgInfo(bgInput);
            /*更新之后重新绑定表*/
            bindList();
        }

        protected void updateDbBgInfo(int bgInput)
        {
            int House_ID = Convert.ToInt32(Session["House_ID"].ToString());
            /*先取出用户对应的所有的房间ID值，用于在下面取selectItem表中的材料对应的价格*/
            string strSql = "SELECT ROOMINFO.ROOM_ID AS ROOM_ID, PRICE_AREA, PRICE_NUM "
                + "FROM HOUSEINFO, ROOMINFO, MATERIAL, SELECTITEM WHERE HOUSEINFO.HOUSE_ID = ROOMINFO.HOUSE_ID "
                + "AND ROOMINFO.ROOM_ID = SELECTITEM.ROOM_ID AND MATERIAL.MAT_ID = SELECTITEM.MAT_ID "
                + "AND HOUSEINFO.HOUSE_ID = '" + House_ID + "';";
            Data.DataTable table = data.GetDataSource(strSql, "Info");
            int idcount = table.Rows.Count;
            string strRoomId = "";
            int[] minP = new int[idcount];
            int min1, min2;
            for (int i = 0; i < (idcount - 1); i++)
            {
                min1 = Convert.ToInt32(table.Rows[i]["Price_Area"].ToString());
                min2 = Convert.ToInt32(table.Rows[i]["Price_Num"].ToString());
                if (min1 < min2)
                    minP[i] = min2;
                else
                    minP[i] = min1;
                strRoomId += "ROOM_ID = '" + Convert.ToInt32(table.Rows[i][0].ToString()) + "' OR ";
            }
            min1 = Convert.ToInt32(table.Rows[idcount - 1]["Price_Area"].ToString());
            min2 = Convert.ToInt32(table.Rows[idcount - 1]["Price_Num"].ToString());
            if (min1 < min2)
                minP[idcount - 1] = min2;
            else
                minP[idcount - 1] = min1;
            strRoomId += "ROOM_ID = '" + Convert.ToInt32(table.Rows[idcount - 1][0].ToString()) + "'";

            string strConnection = ConfigurationManager.ConnectionStrings["connection"].ToString();
            SqlConnection conn = new SqlConnection(strConnection);   ///创建数据库连接
            Data.DataSet ds = new Data.DataSet();

            strSql = "SELECT SELECT_ID, UNIT_PRICE, PRICE FROM SELECTITEM WHERE " + strRoomId;
            SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
            SqlCommandBuilder bd = new SqlCommandBuilder(da);
            //da.InsertCommand = bd.GetInsertCommand();
            try
            {
                da.Fill(ds, "Table");
                Data.DataTable tb = ds.Tables["table"];
                double b1 = Convert.ToDouble(this.tb_bgMin.Text.ToString());
                /*输入总预算除以最低总预算=比例因子,由于之前已经判断输入的总预算大于最低总预算，所以这里的k肯定是大于等于1*/
                double k = ((double)bgInput) / b1;
                int count = tb.Rows.Count;  //表的行数
                double[] num = new double[count];  //用于存放每项材料的数量
                double[] oldUnitP = new double[count];   //原来的预算单价
                double[] oldTotalP = new double[count];  //原来的预算总价
                double[] newUnitP = new double[count];   //新的预算单价
                double[] newTotalP = new double[count];  //新的预算总价
                for (int i = 0; i < count; i++)
                {
                    oldUnitP[i] = Convert.ToDouble(tb.Rows[i]["Unit_Price"].ToString());
                    oldTotalP[i] = Convert.ToDouble(tb.Rows[i]["Price"].ToString());
                    num[i] = oldTotalP[i] / oldUnitP[i];
                    newUnitP[i] = minP[i] * k;  //最低单价*比例因子=新的单价
                    newTotalP[i] = newUnitP[i] * num[i];  //新的单价*数量=新的总价
                    tb.Rows[i]["Unit_Price"] = ((int)newUnitP[i]).ToString();
                    tb.Rows[i]["Price"] = ((int)newTotalP[i]).ToString();
                }
                da.Update(tb);
                tb.AcceptChanges();
                bindList();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                conn.Close();
                da.Dispose();
            }
        }

        protected void gv_budget_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            this.gv_budget.EditIndex = -1;
            bindList();
        }

        protected void gv_budget_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            this.gv_budget.PageIndex = e.NewPageIndex;
            bindList();
        }

        protected void gv_budget_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int House_ID = Convert.ToInt32(Session["House_ID"].ToString());
            string strSql = "SELECT ROOMINFO.ROOM_ID AS ROOM_ID, ROOMINFO.ROOMTYPE AS ROOMTYPE, MATERIAL.MATNAME AS MATNAME, PRICE_AREA, PRICE_NUM, SELECTITEM.UNIT_PRICE AS UNIT_PRICE, SELECTITEM.PRICE AS PRICE "
                 + "FROM HOUSEINFO, ROOMINFO, MATERIAL, SELECTITEM WHERE HOUSEINFO.HOUSE_ID = ROOMINFO.HOUSE_ID "
                 + "AND ROOMINFO.ROOM_ID = SELECTITEM.ROOM_ID AND MATERIAL.MAT_ID = SELECTITEM.MAT_ID "
                 + "AND HOUSEINFO.HOUSE_ID = '" + House_ID + "';";
            Data.DataTable table = data.GetDataSource(strSql, "Info");
            double num = 0;  //用于存放对应材料的数量
            int minP = 0;    //选择项的最低参考价
            int unitP = 0;   //选择项的预算单价
            int totalP = 0;  //选择项的预算总价
            int min1, min2;   //用于比较，取哪个最低价
            min1 = Convert.ToInt32(table.Rows[e.RowIndex]["Price_Area"].ToString());
            min2 = Convert.ToInt32(table.Rows[e.RowIndex]["Price_Num"].ToString());
            if (min1 < min2)
                minP = min2;
            else
                minP = min1;
            unitP = Convert.ToInt32(table.Rows[e.RowIndex]["Unit_Price"].ToString());
            totalP = Convert.ToInt32(table.Rows[e.RowIndex]["Price"].ToString());
            num = (double)totalP / (double)unitP;
            double minTotalP = ((double)minP) * num;
            //取出更新的值
            totalP = Convert.ToInt32((this.gv_budget.Rows[e.RowIndex].FindControl("tb_total") as System.Web.UI.WebControls.TextBox).Text.Trim());
            if (totalP < minTotalP)
            {
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "提示", "<script>alert('请输入您的预算！');</script>");
                return;
            }
            //根据新输入的预算改正材料预算单价
            unitP = (int)((double)totalP / num);
            //取相应的材料名和房间ID，用于查询
            int roomId = Convert.ToInt32((this.gv_budget.Rows[e.RowIndex].FindControl("lb_roomId") as System.Web.UI.WebControls.Label).Text.Trim());
            string matName = (this.gv_budget.Rows[e.RowIndex].FindControl("lb_matName") as System.Web.UI.WebControls.Label).Text.Trim();
            string sql = "UPDATE SELECTITEM SET UNIT_PRICE = '" + unitP + "', PRICE = '" + totalP
                + "' FROM SELECTITEM, MATERIAL WHERE ROOM_ID = '" + roomId
                + "' AND MATERIAL.MAT_ID = SELECTITEM.MAT_ID AND MATERIAL.MATNAME = '" + matName + "'";
            if (data.ExcuteSql(sql))
            {
                this.gv_budget.EditIndex = -1;
                bindList();
            }
            else
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "提示", "<script>alert('更新不成功，请重新操作！');</script>");
        }

        protected void gv_budget_RowEditing(object sender, GridViewEditEventArgs e)
        {
            this.gv_budget.EditIndex = e.NewEditIndex;
            bindList();
        }

        /*这是整个方案订制的最后一步,生成excel表*/
        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
            this.lbExcel.Visible = true;
            //下面从数据库中读取相关数据，准备填入Excel表格中
            int House_ID = Convert.ToInt32(Session["House_ID"].ToString());
            //取出该房子的项目工程信息
            String sql = "SELECT PRO_ID, PROJECTNAME, TOTALPRICE FROM PROJECTINFO WHERE House_ID = '" + House_ID + "'";
            Data.DataTable projectTb = data.GetDataSource(sql, "info");
            int count = projectTb.Rows.Count;
            //根据项目的项数来建表，每个表对应着每个工程项
            Data.DataTable[] matInfoTb = new Data.DataTable[count];
            //用一个二维数组保存上面DataTable数组中每个表的每一行（项）对应的人工费是多少
            int[][] workerP = new int[count][];
            //用一维数组来保存上面DataTable数组每个表的行数
            int[] matInfoCount = new int[count];
            int j = 0;
            int sumRow = 0;
            for (int i = 0; i < count; i++)
            {
                sql = "SELECT MATNAME, ROOMINFO.ROOM_ID, ROOMINFO.ROOMTYPE, PRICE_AREA, PRICE_NUM, UNIT_PRICE, PRICE "
                    + "FROM HOUSEINFO, ROOMINFO, SELECTITEM, MATERIAL "
                    + "WHERE HOUSEINFO.HOUSE_ID = ROOMINFO.HOUSE_ID AND ROOMINFO.ROOM_ID = SELECTITEM.ROOM_ID AND SELECTITEM.MAT_ID = Material.MAT_ID "
                    + "AND HOUSEINFO.HOUSE_ID = '" + House_ID + "' AND MATERIAL.PRONAME = '" + projectTb.Rows[i][1].ToString() + "'";
                matInfoTb[i] = data.GetDataSource(sql, "info");
                //保存每个表的行数
                matInfoCount[i] = matInfoTb[i].Rows.Count;
                sumRow += matInfoCount[i];
                //每个房间铺地砖人工费
                workerP[i] = new int[matInfoCount[i]];
                //根据上面取到了房间ID来算该房间含有的人工费：铺地砖人工费和粉刷墙漆人工费
                if (projectTb.Rows[i][1].ToString().Equals("铺地砖"))
                {
                    string flrP = "";
                    for (j = 0; j < matInfoCount[i]; j++)
                    {
                        //找出表matInfoTb[i]每一行Room_ID对应的地板人工费是多少
                        sql = "SELECT PRICE_WORKER FROM FLOORINFO WHERE ROOM_ID = '" + Convert.ToInt32(matInfoTb[i].Rows[j][1].ToString()) + "'";
                        flrP = data.ExcuteLogin(sql);
                        if (!flrP.Equals("no&found"))
                            workerP[i][j] = Convert.ToInt32(flrP);
                        else
                            workerP[i][j] = 0;
                    }
                }
                else if (projectTb.Rows[i][1].ToString().Equals("粉刷墙漆"))
                {
                    string wallP = "";
                    for (j = 0; j < matInfoCount[i]; j++)
                    {
                        //找出表matInfoTb[i]每一行Room_ID对应的粉刷墙漆人工费是多少
                        sql = "SELECT SALARY_PRICE FROM WALLINFO WHERE ROOM_ID = '" + Convert.ToInt32(matInfoTb[i].Rows[j][1].ToString()) + "'";
                        wallP = data.ExcuteLogin(sql);
                        if (!wallP.Equals("no&found"))
                            workerP[i][j] = Convert.ToInt32(wallP);
                        else
                            workerP[i][j] = 0;
                    }
                }
                else
                {
                    for (j = 0; j < matInfoCount[i]; j++)
                    {
                        workerP[i][j] = 0;
                    }
                }
            }
            //根据表projectTb、数组matInfoCount[i]、表数组matInfoTb、二维数组workerP来填充Excel表格
            //表projectTb每一行都是按顺序对应着表数组matInfoTb中的一个表和二维数组workerP高维
            Application exc = new ApplicationClass();//创建Excel应用程序类实例
            if (exc == null)
            {
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "提示", "<script>alert('创建Excel应用程序失败');</scirpt>");
                return;
            }
            exc.Visible = true;//Excel应用程序可见
            exc.DisplayAlerts = true;//关闭Excel时是否保存的提示对话框
            //使用模版创建新的表格
            Workbook workbook = exc.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            //Worksheet worksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//获得第一个表格
            worksheet.Name = "工程项目及预算表";
            Worksheet proworksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            proworksheet.Name = "工程名称及总价";
            proworksheet.Visible = XlSheetVisibility.xlSheetHidden;
            if (worksheet == null || proworksheet == null)
            {
                Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "提示", "<script>alert('创建Excel表格失败');</scirpt>");
                return;
            }
            Range range = worksheet.get_Range("A1", "L1");//获取单元格范围
            CreateTitle(range, "装修预算一览表");
            range = worksheet.get_Range("A2", "L2");//获取单元格范围
            CreateHead(range, "项目编号|项目名称|材料名|房间编号|房间类型|材料最低单价(元/平方米)|材料最低单价(元/个)|材料预算单价|材料预算总价|人工费|合计|项目合计");
            //添加工程项目信息
            FillProInfo(worksheet, projectTb, matInfoCount);
            //添加对应的材料和预算信息
            FillMatInfo(worksheet, matInfoTb, matInfoCount);
            //添加对应的人工费,并计算合计（材料费加人工费）
            FillWorkerPrice(worksheet, workerP, sumRow);
            //加上总计一栏，计算最后四栏价钱总计
            worksheet.Cells[start + sumRow, 10] = "   总合计(元)：";
            AddTotal(worksheet.get_Range("K" + (start + sumRow), "K" + (start + sumRow)), sumRow);//生成合计行
            range = worksheet.get_Range("A" + (start + sumRow + 3), "F" + (start + sumRow + 3));
            CreateSugs(range, "温馨提示：如需继续阅览或修改预算表，请将该Excel文件保存到本地磁盘。");
            string[,] name = new string[count, 1];
            int[,] price = new int[count, 1];
            for (int i = 0; i < count; i++)
            {
                name[i, 0] = projectTb.Rows[i]["PROJECTNAME"].ToString();
                price[i, 0] = Convert.ToInt32(projectTb.Rows[i]["TOTALPRICE"].ToString());
            }
            range = (Range)proworksheet.Cells[1, 1];
            range = range.get_Resize(count, 1);
            range.Value2 = name;
            range = (Range)proworksheet.Cells[1, 2];
            range = range.get_Resize(count, 1);
            range.Value2 = price;
            //添加饼图
            AddPieChart(workbook, proworksheet.get_Range("B1", "B" + count), proworksheet.get_Range("A1", "A" + count));
            AddLineChart(workbook, proworksheet.get_Range("B1", "B" + count));
            range = null;
            worksheet = null;
            proworksheet = null;
            workbook = null;
            this.lbExcel.Visible = false;
        }

        /*创建表格标题的方法*/
        static void CreateTitle(Range range, string title)
        {
            range.Merge(Missing.Value);//合并表格
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//置中
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Name = "楷体"; //设置字体
            range.Font.Size = 32; //字体大小
            range.Font.Bold = true;
            range.Interior.ColorIndex = 41;
            FillRange(range, title.Split(new char[] { ',' }));
        }
        /*创建表头的方法*/
        static void CreateHead(Range range, string header)
        {
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Name = "黑体"; //设置字体
            range.Font.Size = 13; //字体大小
            range.Interior.ColorIndex = 42;
            range.RowHeight = 44;
            range.ColumnWidth = 14;
            range.WrapText = true;
            FillRange(range, header.Split(new char[] { '|' }));
        }
        /*填充表格范围的方法*/
        static void FillRange(Range range, object[] data)
        {
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, data);
            range = null;
        }
        /*填充工程项目信息*/
        static void FillProInfo(Worksheet worksheet, Data.DataTable table, int[] count)
        {
            int rowStart = start;
            int rowFinish = 0;
            string rangeS = "";
            string rangeF = "";
            int rowC = table.Rows.Count;
            int colC = table.Columns.Count;
            string[] strCol = new string[colC];
            //设置列位置
            strCol[0] = "A";
            strCol[1] = "B";
            strCol[2] = "L";
            //把table转化成二维数组
            //string[][] strTb = TableToArray(table);
            int j = 0;
            Range range;
            for (int i = 0; i < colC; i++)
            {
                rowStart = start;
                rowFinish = 0;
                for (j = 0; j < rowC; j++)
                {
                    rowFinish = rowStart + count[j] - 1;
                    rangeS = strCol[i] + rowStart;
                    rangeF = strCol[i] + rowFinish;
                    //分别填充项目编号、项目名称、项目总价
                    range = worksheet.get_Range(rangeS, rangeF);
                    range.Merge(Missing.Value);//合并表格
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//置中
                    range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    range.WrapText = true;
                    FillRange(range, table.Rows[j][i].ToString().Split(new char[] { ',' }));
                    rowStart = rowFinish + 1;
                }
            }
            rowStart = start;
            //填充颜色，便于识别
            for (j = 0; j < rowC; j++)
            {
                range = worksheet.get_Range(strCol[0] + rowStart, strCol[2] + (rowStart + count[j] - 1));
                range.Interior.ColorIndex = 34 + j;
                rowStart += count[j];
            }
            range = null;
        }
        /*填充材料和对应预算信息*/
        static void FillMatInfo(Worksheet worksheet, Data.DataTable[] table, int[] count)
        {
            int rowStart = start;
            int colStart = 3;
            int tbCount = count.Length;
            Range range;
            for (int i = 0; i < tbCount; i++)
            {
                string[,] tbStr = TableToArray(table[i]);
                range = (Range)worksheet.Cells[rowStart, colStart];
                range = range.get_Resize(count[i], table[i].Columns.Count);
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.RowHeight = 22;
                range.WrapText = true;
                range.Value2 = tbStr;
                rowStart += count[i];
            }
            range = null;
        }
        static void FillWorkerPrice(Worksheet worksheet, int[][] workP, int sumRow)
        {
            Range range;
            int tbRow = workP.GetLength(0);
            int curRow = 0;
            int[,] wkp = new int[sumRow, 1];
            for (int i = 0; i < tbRow; i++)
            {
                for (int j = 0; j < workP[i].Length; j++)
                {
                    wkp[curRow, 0] = workP[i][j];
                    curRow++;
                }
            }
            range = (Range)worksheet.Cells[3, 10];
            range = range.get_Resize(sumRow, 1);
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.RowHeight = 22;
            range.WrapText = true;
            range.Value2 = wkp;
            range = worksheet.get_Range("K" + start, "K" + (sumRow + start - 1));
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.RowHeight = 22;
            range.WrapText = true;
            range.FormulaR1C1 = "=RC[-2] + RC[-1]";
            range = null;
        }
        static string[,] TableToArray(Data.DataTable table)
        {
            int rowC = table.Rows.Count;
            int colC = table.Columns.Count;
            string[,] strTb = new string[rowC, colC];
            int j = 0;
            for (int i = 0; i < rowC; i++)
            {
                for (j = 0; j < colC; j++)
                {
                    strTb[i, j] = table.Rows[i][j].ToString();
                }
            }
            return strTb;
        }
        static void AddTotal(Range range, int rows)//添加“合计”行的方法
        {
            string strLocation = ((int)(rows + 1)).ToString();//行定位
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.RowHeight = 22;
            range.WrapText = true;
            range.FormulaR1C1 = "=SUM(R[-" + strLocation + "]C:R[-1]C)";//相对上面第几行求和
            range = null;
        }
        /*创建提示消息*/
        static void CreateSugs(Range range, string info)
        {
            range.Merge(Missing.Value);
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//置中
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.RowHeight = 22;
            range.Font.Name = "楷体"; //设置字体
            range.Font.Size = 12; //字体大小
            range.WrapText = true;
            range.Font.Bold = true;
            FillRange(range, info.Split(new char[] { ',' }));
        }
        static void AddPieChart(Workbook workbook, Range datarange, Range titlerange)
        {
            //创建图表类实例
            Chart Piechart = (Chart)workbook.Charts.Add(Missing.Value,
                workbook.ActiveSheet, Missing.Value, Missing.Value);
            Piechart.Name = "饼状图";
            Piechart.ChartType = XlChartType.xl3DPie;//设置图表类型
            Piechart.SetSourceData(datarange, XlRowCol.xlColumns);//设置图表数据源
            Series category = (Series)Piechart.SeriesCollection(1);//分类系列类实例
            category.XValues = titlerange;//分类系列的范围
            //设置图表数据标签
            category.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowLabelAndPercent,
                Missing.Value, true, true, false, true, false, true, false, Missing.Value);
            ((Series)Piechart.SeriesCollection(1)).Values = datarange;//设置系列值来源
            datarange = null;
            Piechart = null;
        }
        static void AddLineChart(Workbook workbook, Range datarange)//添加折线图的方法
        {
            Chart linechart = (Chart)workbook.Charts.Add(Missing.Value, workbook.ActiveSheet,
               Missing.Value, Missing.Value);//创建图表类实例
            linechart.Name = "折线图";
            Axis xAxis = (Axis)linechart.Axes(XlAxisType.xlCategory,
              XlAxisGroup.xlPrimary);//X坐标轴类实例
            xAxis.HasTitle = true;//设置标题
            xAxis.AxisTitle.Text = "工程项目";//设置X坐标的标题文字
            Axis yAxis = (Axis)linechart.Axes(XlAxisType.xlValue,
              XlAxisGroup.xlPrimary);//Y坐标轴类实例
            yAxis.HasTitle = true;//设置标题
            yAxis.AxisTitle.Text = "预算";//设置Y坐标的标题文字
            linechart.ChartType = XlChartType.xlLine;//设置图表类型
            linechart.SetSourceData(datarange, XlRowCol.xlColumns);//设置图表数据源
            datarange = null;
            linechart = null;
        }
    }
}
